import ast
import numpy as np
import pandas as pd
from Efficientnet.distance import calculate_confidence_interval


def main(colname, col):
    # 替换为你的 Excel 文件路径
    file_path = './output_new_3.xlsx'  # 修改为你的文件路径
    # 读取xlsx文件
    df = pd.read_excel(file_path)

    # 选择包含字符串列表的列
    column_name = colname  # 替换成你实际的列名
    string_data = df[column_name]

    # 使用 ast.literal_eval 解析字符串为实际的列表
    list_data = [ast.literal_eval(item) for item in string_data]

    # 转换为 NumPy 数组
    np_array = np.array(list_data)

    # 提取第一列（索引为0的列）
    selected_column = np_array[:, col]

    # 将数据进行排序
    sorted_data = sorted(selected_column)
    print(f"第{colname}列第{col}位数据：")
    if(colname == "prob_source" or colname == "prob_Dclose1"):
        pass
    else:
        computeDistribution(sorted_data, colname, col)

    # Calculate 95% confidence interval for population mean weight using t-distribution
    a_t, b_t = calculate_confidence_interval(sorted_data, method='t')
    # Calculate 95% confidence interval for population mean weight using normal distribution
    c_norm, d_norm = calculate_confidence_interval(sorted_data, method='normal')
    print("T-Distribution Interval:", a_t, b_t)
    print("Normal Distribution Interval:", c_norm, d_norm)
    print("########################")


def computeDistribution(sorted_data, colname, col):
    # 设置你想要的百分位数（比如说你想要得到超过95%的数据）
    if(colname == "prob_Dclose2"):
        desired_percentile = 95
    elif(colname == "prob_Ddistance"):
        desired_percentile = 85

    # 计算超过特定百分位数的索引位置
    index = int((desired_percentile / 100) * len(sorted_data))

    # 获取对应索引位置的值
    desired_value = sorted_data[index]
    print(f"超过第{desired_percentile}% 的数据的值是: {desired_value}")
    print(f"数据范围为: {100 - desired_percentile}%")


if __name__ == '__main__':
    main("prob_source", 0)
    main("prob_source", 1)
    main("prob_source", 2)

    main("prob_Dclose1", 0)
    main("prob_Dclose1", 1)
    main("prob_Dclose1", 2)

    main("prob_Dclose2", 0)
    main("prob_Dclose2", 1)
    main("prob_Dclose2", 2)

    main("prob_Ddistance", 0)
    main("prob_Ddistance", 1)
    main("prob_Ddistance", 2)
